import datetime

from sqlalchemy import create_engine, Table, Column, Integer, String, DateTime, func, delete, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

from entities import *

import config

# engine = create_engine('sqlite:///database.db', echo=True)
engine = create_engine(
    # 'mysql+pymysql://flashx:1qaz!QAZ@rm-2zeb6600264zf2zwkko.mysql.rds.aliyuncs.com:3306/flashx?charset=utf8mb4',
    'mysql+pymysql://flashx:1qaz!QAZ@rm-2zeb6600264zf2zwkko.mysql.rds.aliyuncs.com:3306/flashx?charset=utf8mb4',
    pool_size=5,
    max_overflow=20,
    pool_recycle=300,
    echo=True
)
Session = sessionmaker(bind=engine)
session = Session()


class DbHelper:
    def __init__(self):
        # Base.metadata.drop_all()
        Base.metadata.create_all(engine)
        # self.engine = create_engine('sqlite:///database.db', echo=True)
        # self.fields_club_members = 'club_id,sex,member_id,user_id,level,phone,member_name,member_avatar'.split(',')

    def commit(self):
        session.commit()

    def bind(self, openid, phone):
        member = session.query(ClubMember).where(ClubMember.phone == phone).first()
        if member is None:
            return None
        # print('member is:', member.member_id)
        member_id = member.member_id
        member_ext = session.query(MemberExt).where(MemberExt.member_id == member_id).first()
        if member_ext is None:
            member_ext = MemberExt(
                member_id=member_id,
                member_name=member.member_name,
                avatar=member.member_avatar,
                points=0,
                locked_points=0,
                act_times=0,
                openid=openid,
                game_times=0,
                update_time=datetime.datetime.now()
            )
            session.add(member_ext)
        else:
            member_ext.openid = openid

        session.commit()
        return member_ext

    def find_activity(self, act_id):
        return session.query(Activity).where(Activity.act_id == act_id).first()
        # act = query.first()

    def find_member_ex(self, openid):
        return session.query(MemberExt).where(MemberExt.openid == openid).first()

    def find_member(self, member_id):
        return session.query(ClubMember).where(ClubMember.member_id == member_id).first()

    def find_check_in(self, member_id, date):
        return session.query(MemberCheckIn).where(MemberCheckIn.member_id == member_id,
                                                  MemberCheckIn.check_in_date == date).first()

    def save_activity(self, act_data):
        act_entity = Activity(
            act_id=act_data['id'],
            club_id=act_data['clubId'],
            act_type=act_data['type'],
            act_name=act_data['activityTitle'],
            act_date=act_data['activityDate'],
            start_time=act_data['startTime'],
            end_time=act_data['endTime'],
            apply_count=act_data['applyMemberNum'],
            new_member_count=act_data['newMemberCount'],
            male_count=act_data['maleCount'],
            female_count=act_data['femaleCount'],
            # newbie_count=act_data['newbieCount'],
            place_id=act_data['placeId'],
            place_name=act_data['activityAddress'],
            give_out_points=act_data['points']
        )
        session.add(act_entity)

    def add_member_points(self, club_id, member_id, member_name, avatar, points, reason, banker, remark, operator,
                          target_id, join_date):
        # 更新用户积分
        member = session.query(MemberExt).where(MemberExt.member_id == member_id).first()
        if member is None:
            member = MemberExt(member_id=member_id, points=points, member_name=member_name, avatar=avatar,
                               update_time=datetime.datetime.now(), locked_points=0, act_times=0)
            session.add(member)
        else:
            member.points = member.points + points
            member.act_times = member.act_times + 1
            member.update_time = datetime.datetime.now()
            member.avatar = avatar
            member.member_name = member_name

        # 增加积分变更记录
        points_log = PointHistory(
            club_id=club_id,
            member_id=member_id,
            member_name=member_name,
            points=points,
            reason=reason,
            banker=banker,
            remark=remark,
            operator=operator,
            create_time=datetime.datetime.now(),
            target_id=target_id,
            join_date=join_date
        )
        session.add(points_log)

    def update_members(self, club_id, members):
        print('保存数据：', len(members))
        session.execute(delete(ClubMember))
        female_count = 0
        male_count = 0
        for member in members:
            if member['sex'] == 0:
                female_count = female_count + 1
            if member['sex'] == 1:
                male_count = male_count + 1

            new_member = ClubMember(club_id=member['clubId'],
                                    sex=member['sex'],
                                    member_id=member['memberId'],
                                    user_id=member['userId'],
                                    level=member['level'],
                                    phone=member['phone'],
                                    member_name=member['memberName'],
                                    member_avatar=member['memberAvatar']
                                    )
            session.add(new_member)

        history = ClubHistory(
            club_id=club_id,
            member_count=len(members),
            female_count=female_count,
            male_count=male_count,
            update_date=datetime.datetime.now().strftime('%Y-%m-%d')
        )
        session.add(history)

        session.commit()

    def delete_member(self):
        session.execute(delete(ClubMember))
        session.commit()

    def check_in(self, openid):
        ex = session.query(MemberExt).where(MemberExt.openid == openid).first()
        today = datetime.datetime.now().strftime('%Y-%m-%d')
        data = MemberCheckIn(
            club_id=config.club_id,
            member_id=ex.member_id,
            check_in_date=today,
            create_time=datetime.datetime.now()
        )
        session.add(data)

        # 每次签到发放1积分
        # date = datetime.datetime.strftime('%Y-%m-%d')
        self.add_member_points(config.club_id, ex.member_id, ex.member_name, ex.avatar,
                               config.CHECK_IN_POINTS, today + ' 签到积分', '-', '',
                               'System', None, today)

        session.commit()
        return True

    def get_points_history(self, openid):
        ex = self.find_member_ex(openid)
        member_id = ex.member_id
        results = session.query(PointHistory).filter(PointHistory.member_id == member_id).order_by(
            PointHistory.id.desc()).limit(100).all()
        return results

    def check_in_status(self, openid):
        print('check in status:', openid)
        member = session.query(MemberExt).where(MemberExt.openid == openid).first()
        if not member:
            return -1
        today = datetime.datetime.now().strftime('%Y-%m-%d')
        data = session.query(MemberCheckIn).where(MemberCheckIn.member_id == member.member_id,
                                                  MemberCheckIn.check_in_date == today).first()
        if data is None:
            return 0
        else:
            return 1

    def is_phone_exist(self, phone):
        member = session.query(ClubMember).where(ClubMember.phone == phone).first()
        return True if member is not None else False

    def find_top_members(self):
        rows = session.query(ClubMember.member_name, ClubMember.member_avatar, MemberExt.points).join(MemberExt, ClubMember.member_id==MemberExt.member_id).order_by(MemberExt.points.desc()).limit(10).all()
        rets = []
        for row in rows:
            rets.append(row._asdict())
        return rets

    def get_admin_account(self):
        account = session.query(FlashXAccount).filter(FlashXAccount.is_admin == 1).first()
        return account

    def update_account_account(self, phone, token):
        print('保存数据============================', phone, token)
        account = self.get_user_account(phone)
        account.access_token = token
        account.last_update = datetime.datetime.now()
        session.commit()

    def get_user_account(self, phone):
        account = session.query(FlashXAccount).filter(FlashXAccount.phone == phone).first()
        return account

    def get_user_accounts(self):
        accounts = session.query(FlashXAccount).filter(FlashXAccount.is_admin == 0).all()
        return accounts

    def create_account(self, phone, password, name, token):
        account = FlashXAccount(
            phone=phone,
            password=password,
            member_name=name,
            access_token=token,
            last_update=datetime.datetime.now(),
            is_admin=0
        )
        session.add(account)
        session.commit()

    def find_comment(self, cmt_id):
        cmt = session.query(UserComments).filter(UserComments.comment_id == cmt_id).first()
        return cmt

    def create_comment(self, cmt_id, content, image_url):
        comment = UserComments(
            comment_id=cmt_id,
            content=content,
            image_url=image_url,
            is_used=0
        )
        session.add(comment)
        session.commit()

    def get_next_act(self):
        today = datetime.datetime.now().strftime('%Y-%m-%d') + ' 00:00:00'
        hour = datetime.datetime.now().hour
        acts = session.query(TempActivity).filter(TempActivity.start_hour <= hour + 3, TempActivity.start_hour > hour, TempActivity.last_create_date < today).all()
        for act in acts:
            act.last_create_date = datetime.datetime.now()
        session.commit()
        return acts

    def reset_temp_act(self):
        acts = session.query(TempActivity).all()
        for act in acts:
            act.last_create_date = None
        session.commit()

    def get_one_nickname(self):
        nn = session.query(NickNames).filter(NickNames.is_used == 0).first()
        nn.is_used = 1
        session.commit()
        return nn

    def get_one_phone_number(self):
        ret = session.query(PhoneNumbers).filter(PhoneNumbers.is_used == 0).first()
        return None if ret is None else ret.phone

    def update_phone_number(self, phone, is_valid):
        ret = session.query(PhoneNumbers).filter(PhoneNumbers.phone == phone).first()
        ret.is_used = 1
        ret.is_valid = is_valid
        session.commit()

    def get_apply_account(self):
        account = session.query(FlashXAccount).order_by(FlashXAccount.last_use).first()
        account.last_use = datetime.datetime.now()
        session.commit()
        return account

    def get_random_member(self):
        member = session.query(ClubMember).order_by(func.random()).limit(1).first()
        return member

    def get_random_comment(self):
        comment = session.query(UserComments).filter(UserComments.is_used == 0).order_by(func.random()).first()
        comment.is_used = 1
        session.commit()
        return comment

    ######################################## 以下部分为20251122比赛专用 ######################################################
    def get_game_score(self):
        return session.query(GameScore).all()

    def get_pre_score(self):
        return session.query(PreFinalScore).all()

    def get_final_score(self):
        return session.query(FinalScore).all()

    def save_game_score(self, team1, team2, score1, score2):
        gs = session.query(GameScore).filter(GameScore.team1 == team1, GameScore.team2 == team2).first()
        gs.score1 = score1
        gs.score2 = score2
        gs.record_time = datetime.datetime.now()
        session.commit()

    def save_pre_score(self, team1, team2, score1, score2, type):
        gs = session.query(PreFinalScore).filter(PreFinalScore.team1 == team1, PreFinalScore.team2 == team2, PreFinalScore.game_type == type).first()
        if gs:
            gs.score1 = score1
            gs.score2 = score2
        else:
            score = PreFinalScore(
                team1=team1,
                team2=team2,
                score1=score1,
                score2=score2,
                game_type=type
            )
            session.add(score)
        session.commit()

    def save_final_score(self, team1, team2, score1, score2, type):
        gs = session.query(FinalScore).filter(FinalScore.team1 == team1, FinalScore.team2 == team2, FinalScore.game_type == type).first()
        if gs:
            gs.score1 = score1
            gs.score2 = score2
        else:
            score = FinalScore(
                team1=team1,
                team2=team2,
                score1=score1,
                score2=score2,
                game_type=type
            )
            session.add(score)
        session.commit()


    ######################################## 以下部分为头条投放效果分析专用 ######################################################
    def find_top_data(self, start_time, end_time):
        data = (session.query(TopActivity)
                .where(TopActivity.create_time >= start_time, TopActivity.create_time < end_time)
                .order_by(TopActivity.view_count.desc())
                .all())
        return data

    def find_top_activity(self, activity_id):
        # 头条按小时投放，此处也需要按小时查询
        now = datetime.datetime.now()
        date = now.strftime('%Y-%m-%d')
        hour = now.hour
        return (session.query(TopActivity)
                .where(TopActivity.activity_id == activity_id,
                       TopActivity.collect_date == date,
                       TopActivity.collect_hour == hour).first())

    def save_top_activities(self, acts):

        now = datetime.datetime.now()
        # minute = now.minute
        for act in acts:
            saved_act = self.find_top_activity(act['id'])
            if saved_act is None:
                top_act = TopActivity(
                    activity_id=act['id'],
                    club_id=act['clubId'],
                    club_name=act['organizerName'],
                    club_level=act['organizerInfoDto']['clubBadge'],
                    address=act['activityAddress'],
                    activity_date=act['activityDate'],
                    activity_time=act['activityTime'],
                    activity_title=act['activityTitle'],
                    activity_type=act['activityType'],
                    activity_avatar=act['activityAvatar'],
                    apply_count_start=act['applyMemberNum'],
                    new_member_count_start=act['new_member_count'],
                    new_member_count_end=act['new_member_count'],
                    need_count=act['needMemberNum'],
                    is_free_play=act['isSupportFreePlay'],
                    free_play_count=act['freePlayCount'],
                    level_lower=act['levelLower'],
                    level_upper=act['levelUpper'],
                    level_average=0,
                    region=act['region'],
                    # start_time=datetime.datetime.fromtimestamp(act['startTimestamp']),
                    start_time=act['startTime'],
                    end_time=act['endTime'],
                    show_cost=act['showCost'],
                    display_position=act['position'],
                    collect_date=now.strftime('%Y-%m-%d'),
                    collect_hour=now.hour,
                    collect_weekday=now.weekday(),
                    male_count_start=act['male_count'],
                    female_count_start=act['female_count']
                )
                session.add(top_act)
            else:
                # 已存在的活动，更新数据
                saved_act.apply_count_end = act['applyMemberNum']
                saved_act.new_member_count_end = act['new_member_count']
                saved_act.level_average = act['level_average']
                saved_act.view_count = act['viewCount']
                saved_act.male_count_end = act['male_count']
                saved_act.female_count_end = act['female_count']
                saved_act.update_time = now

            session.commit()



